package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.UserDetail;

public class UserDetailDao {

	public UserDetailDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";
	//String pass = "webclass";

	// 퀘스트 내 답변 불러오기
	public UserDetail getUserDetailInfo(int userId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		UserDetail userDetail = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select detailId, uid, ongoingStudy, clearStudy, clearQuest, totalQuest from userDetail where uid=?");
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int detailId = rs.getInt("detailId");
				int uid = rs.getInt("uid");
				int ongoingStudy = rs.getInt("ongoingStudy");
				int clearStudy = rs.getInt("clearStudy");
				int clearQuest = rs.getInt("clearQuest");
				int totalQuest = rs.getInt("totalQuest");

				userDetail = new UserDetail(detailId, uid, ongoingStudy,
						clearStudy, clearQuest, totalQuest);
			}

			return userDetail;

		} catch (Exception ex) {
			ex.printStackTrace();
			return userDetail;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// userdetail 회원가입시 자동 생성
	public void insertUserDetailInfo(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into userDetail(uid, ongoingStudy, clearStudy, clearQuest, totalQuest) values(?,0,0,0,0)");
			pstmt.setInt(1, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 가입 수락 할 시 같이 호출
	public void updateOngoingStudy(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update userDetail set ongoingStudy=ongoingStudy+1 where uid=?");
			pstmt.setInt(1, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 가입 수락 할 시 같이 호출
	public void updateClearStudy(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update userDetail set clearStudy=clearStudy+1, ongoingStudy=ongoingStudy-1 where uid=?");
			pstmt.setInt(1, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 퀘스트 답변 작성할 시 같이 호출
	public void updateClearQuest(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update userDetail set clearQuest=clearQuest+1 where uid=?");
			pstmt.setInt(1, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}
	

	// 퀘스트 작성할 시 같이 호출
	public void updateTotalQuest(int sid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select uid from participation where sid=?");
			pstmt.setInt(1, sid);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				int uid = rs.getInt("uid");
				pstmt = con
						.prepareStatement("update userDetail set totalQuest=totalQuest+1 where uid=?");
				pstmt.setInt(1, uid);

				pstmt.executeUpdate();
			}

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}